package com.bst.common.utils.sql;

import com.bst.common.constant.JobConstant;
import com.bst.common.utils.DateUtil;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EtlUtil {
    public static final String DT_BEGIN_KEY = "${DT_BEGIN}";//date类型的日期的开始
    public static final String DT_DAY_BEGIN_INT_KEY = "${DT_DAY_BEGIN_INT}";//date类型的日期的开始
    public static final String D_BEGIN_KEY = "${D_BEGIN}";//字符串类型的日期开始
    public static final String DT_END_KEY = "${DT_END}";//date类型的日期的开始
    public static final String DT_DAY_END_INT_KEY = "${DT_DAY_END_INT}";//date类型的日期的开始
    public static final String D_END_KEY = "${D_END}";//字符串类型的日期结束

    public static final String D_BEGIN_KEY_ = "${D_BEGIN2}";//字符串类型的日期开始
    public static final String D_END_KEY_ = "${D_END2}";//字符串类型的结束日期
    //public static final String SD_ORG="${"

    public static String getMaxNumSql(String sql,Integer pageSize,String dbType,String order) {
        order = StringUtils.isEmpty(order)?"":("order by "+order);
        if(JobConstant.DB_TYPE_MYSQL.equals(dbType)) {
            sql = "select *　from ("+sql+") aa "+order+" limit "+pageSize;
        } else if (JobConstant.DB_TYPE_SQLSERVER.equals(dbType)) {
            sql = "select top "+pageSize+" *　from ("+sql+") aa "+order;
        } else if (JobConstant.DB_TYPE_ORACLE.equals(dbType)) {
            sql = "select *　from ("+sql+") aa "+order+" where rownum< "+pageSize;
        }  else if (JobConstant.DB_TYPE_POSTGREY.equals(dbType)) {
            sql = "select *　from ("+sql+") aa "+order+" limit "+pageSize;
        }
        return sql;
    }
    /**
     * 填充参数
     *
     * @param start
     * @param end
     * @return
     */
    public static Map<String, String> getGlobalParam(Date start, Date end, String dbType) {
        Map<String, String> map = new LinkedHashMap<>();
        String beginDayStr = DateUtil.toDateStrByFormat(start, "yyyy-MM-dd HH:mm:ss");
        String endDayStr = DateUtil.toDateStrByFormat(end, "yyyy-MM-dd HH:mm:ss");
        String dayBegin = DateUtil.toDateStrByFormat(start, "yyyy-MM-dd");
        String dayEnd = DateUtil.toDateStrByFormat(end, "yyyy-MM-dd");
        if (dbType.equals(JobConstant.DB_TYPE_MYSQL) || dbType.equals(JobConstant.DB_TYPE_POSTGREY)) {

        } else if (dbType.equals(JobConstant.DB_TYPE_ORACLE)) {
            map.put(DT_BEGIN_KEY, "to_date ('" + dayBegin + "','yyyy-mm-dd')");
            map.put(DT_END_KEY, "to_date ('" + dayEnd + "','yyyy-mm-dd')");
        } else {
            map.put(DT_BEGIN_KEY, "'" + beginDayStr + "'");
            map.put(DT_END_KEY, "'" + endDayStr + "'");
        }
        map.put(D_BEGIN_KEY, "'" + dayBegin + "'");
        map.put(DT_DAY_BEGIN_INT_KEY, dayBegin);
        map.put(D_END_KEY, "'" + dayEnd + "'");
        map.put(DT_DAY_END_INT_KEY, dayEnd);
        map.put(D_BEGIN_KEY_, "'" + dayBegin + "'");
        map.put(D_END_KEY_, "'" + dayEnd + "'");
        return map;
    }

    public static String buildSql(String sql, Date startTime, Date endTime, String dbType) {
        Map<String, String> map = EtlUtil.getGlobalParam(startTime, endTime, dbType);

        for (Map.Entry<String, String> entry : map.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            while (sql.indexOf(key) != -1)
                sql = sql.replace(key, value);
        }
        return sql;
    }

    private static final String VALUE_HOLDER = "?";

    public static String getInsertSQL(List<String> columns, String table) {
        List<String> columnHolders = new ArrayList<>(columns.size());
        List<String> valueHolders = new ArrayList<>(columns.size());

        for (String columnHolder : columns) {
            valueHolders.add(VALUE_HOLDER);
            columnHolders.add(columnHolder);
        }
        return new StringBuilder()
                .append("INSERT INTO ").append(table).append(" (").append(StringUtils.join(columnHolders, ","))
                .append(") VALUES(").append(StringUtils.join(valueHolders, ","))
                .append(")").toString();
    }



    /**
     * sql变量替换
     * @return
     */
    public static String dataXConfigVariHandler(String dayBegin,String dayEnd, String config) {
        ObjectMapper objectMapper = new ObjectMapper();
        JsonNode rootNode = null;
        try {
            rootNode = objectMapper.readTree(config);
            JsonNode readerNote = rootNode.get("job").findValues("reader").get(0);
            JsonNode writerNote = rootNode.get("job").findValues("writer").get(0);
            JsonNode whereSqlNode = readerNote.get("parameter").get("where");
            String whereSql = (whereSqlNode==null?"":whereSqlNode.textValue());
            String readJdbcUrl = readerNote.findPath("jdbcUrl").get(0).asText();
            JsonNode querySqlNote = readerNote.findPath("querySql").get(0);
            String querySql = querySqlNote==null?"":querySqlNote.asText();
            if(StringUtils.isNotEmpty(whereSql)) {
                if(readJdbcUrl.contains("oracle")) {
                    ((ObjectNode)readerNote.get("parameter")).put("where",variReplact(whereSql,dayBegin,dayEnd,JobConstant.DB_TYPE_ORACLE));
                } else {
                    ((ObjectNode)readerNote.get("parameter")).put("where",variReplact(whereSql,dayBegin,dayEnd,JobConstant.DB_TYPE_MYSQL));
                }
            }
            if(StringUtils.isNotEmpty(querySql)) {
                if(readJdbcUrl.contains("oracle")) {
                    querySql = variReplact(querySql,dayBegin,dayEnd,JobConstant.DB_TYPE_ORACLE);
                } else {
                    querySql = variReplact(querySql,dayBegin,dayEnd,JobConstant.DB_TYPE_MYSQL);
                }
                ArrayNode arrayNode = objectMapper.valueToTree(new String[]{querySql});
                ((ObjectNode)readerNote.get("parameter").get("connection").get(0)).putArray("querySql").addAll(arrayNode);
            }
            JsonNode preSqlNode = writerNote.findPath("preSql").get(0);
            String preSql = (preSqlNode==null?null:preSqlNode.asText());
            if(StringUtils.isNotEmpty(preSql)) {
                String writeJdbcUrl = writerNote.findPath("jdbcUrl").asText();
                if(writeJdbcUrl.contains("oracle")) {
                    preSql = variReplact(preSql,dayBegin,dayEnd,JobConstant.DB_TYPE_ORACLE);
                } else {
                    preSql = variReplact(preSql,dayBegin,dayEnd,JobConstant.DB_TYPE_MYSQL);
                }
                ArrayNode arrayNode = objectMapper.valueToTree(new String[]{preSql});
                ((ObjectNode)writerNote.get("parameter")).putArray("preSql").addAll(arrayNode);
            }
            return objectMapper.writeValueAsString(rootNode);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String variReplact(String config,String dayBegin,String dayEnd,String dbType) {
        if (dbType.equals(JobConstant.DB_TYPE_ORACLE)) {
            if(com.bst.common.utils.StringUtils.isNotEmpty(dayBegin)) {
                config = config.replaceAll("'\\$\\{DT_BEGIN\\}'","to_date ('" + dayBegin + "','yyyy-mm-dd')");
                config = config.replaceAll("\\$\\{DT_BEGIN\\}","to_date ('" + dayBegin + "','yyyy-mm-dd')");
            }
            if(com.bst.common.utils.StringUtils.isNotEmpty(dayEnd)) {
                config = config.replaceAll("'\\$\\{DT_END\\}'","to_date ('" + dayEnd + "','yyyy-mm-dd')");
                config = config.replaceAll("\\$\\{DT_END\\}","to_date ('" + dayEnd + "','yyyy-mm-dd')");
            }
        } else {
            if(com.bst.common.utils.StringUtils.isNotEmpty(dayBegin)) {
                config = config.replaceAll("\\$\\{DT_BEGIN\\}",dayBegin);
            }
            if(com.bst.common.utils.StringUtils.isNotEmpty(dayEnd)) {
                config = config.replaceAll("\\$\\{DT_END\\}",dayEnd);
            }
        }
        config = config.replaceAll("\\$\\{DAY_BEGIN\\}",dayBegin);
        config = config.replaceAll("\\$\\{DAY_END\\}",dayEnd);
        config = config.replaceAll("\\$\\{YEAR\\}",dayBegin.substring(0,4));
        return config;
    }

    public static String variReplactByParamMap(String sql,Map<String,String> paramMap) {
        if(paramMap==null || paramMap.size()==0) {
            return sql;
        }
        for (Map.Entry<String, String> entry : paramMap.entrySet()) {
            sql =  sql.replaceAll("'\\$\\{"+entry.getKey()+"\\}'",entry.getValue());
        }
        return sql;
    }

    public static List<String> getParamFromSql(String sql) {
        String pattern = "\\$\\{[^}(?!{)]*\\}";
        Pattern p = Pattern.compile(pattern);
        Matcher m = p.matcher(sql);
        List<String> params = new ArrayList<>();
        while(m.find()){
            // ${}和 里面的内容
            String var = m.group(0);
            var = var.replaceAll("\\$","").replaceAll("\\{","").replaceAll("\\}","");
            if(!params.contains(var)) {
                params.add(var);
            }
        }
        return params;
    }

    public static String getIndexValueFld(String euValtp) {
        String valFld;
        if(JobConstant.VALUE_FLOAT.equals(euValtp)) { //小数插入fl
            valFld = "VALUE_FLOAT";
        } else if(JobConstant.VALUE_INT.equals(euValtp)) { //小数插入fl
            valFld = "VALUE_INT";
        } else {
            valFld = "VALUE_FLOAT";
        }
        return valFld;
    }

}
